package AirlineControl;

import java.util.ArrayList;
import java.util.Date;
import java.util.Calendar;
import java.sql.*;

public class Airplane {

		private int flightID;
    private int numPassengers;
    private int seatsAvailable;
    private int seatsBooked;
		private String type;
		private String destination;
		private String source;
    private Calendar bookDate;
		private Calendar deptDate; 
		private Calendar arrivalDate;
    private int inFlight;
    private int deptTime;
    private int arrivalTime; 
    private String interstop1;
    private String interstop2;
	
   
   
    /**
    * Creates a passenger and insets the info into the database
    * @param timestamps - an array that contains all the Date values
    * @param numbers - an array that contains all the integer values
    * @param strings - an array that contains all the string values
    * 
    */
    public Airplane(java.util.Calendar [] timestamps, int [] numbers, String [] strings){
                  
            try {
             Class.forName("oracle.jdbc.driver.OracleDriver");
            }
            catch(ClassNotFoundException ex) {
            ex.printStackTrace(System.err);
            System.exit(1);
            }
            
            String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
                Connection con = null;
                try {
                        con = DriverManager.getConnection(url, "bmill1", "bmill1");
                        System.out.println("\nCreating a flight.");
                        System.out.println("---------------");
                }
                catch (SQLException se ){
                        System.out.println("Unable to connect to Oracle.");
                        se.printStackTrace();
                        System.exit(1);
                }
                
                
                    this.bookDate = timestamps[0];
                    this.deptDate = timestamps[1];
                    this.arrivalDate = timestamps[2];
           
                    this.flightID = numbers[0];
                    this.seatsBooked = 0;
                    this.numPassengers = 0;
                    this.deptTime = numbers[1];
                    this.arrivalTime = numbers[2];
                    this.inFlight = 0;
                    
                    this.type = strings[0];
                    this.destination = strings[1];
                    try{
                      Statement stmt = con.createStatement();
                      ResultSet result = stmt.executeQuery("SELECT cityid from cities where citystate='" + destination + "'");
                      result.next();
                      destination = result.getString("cityid");

                    }
                    catch (SQLException se ){
			 System.out.println("Unable to connect to Oracle.");
			 se.printStackTrace();
			 System.exit(1);
		      }  
                    this.source = strings[2];
                    try{
                      Statement stmt = con.createStatement();
                      ResultSet result = stmt.executeQuery("SELECT cityid from cities where citystate='" + source + "'");
                      result.next();
                      source = result.getString("cityid");

                    }
                    catch (SQLException se ){
			 System.out.println("Unable to connect to Oracle.");
			 se.printStackTrace();
			 System.exit(1);
		      }
             
                    if (this.interstop1 != null){
                    this.interstop1 = strings[3];
                    try{
                      Statement stmt = con.createStatement();
                      ResultSet result = stmt.executeQuery("SELECT cityid from cities where citystate='" + interstop1 + "'");
                      result.next();
                      interstop1 = result.getString("cityid");

                    }
                    catch (SQLException se ){
			 System.out.println("Unable to connect to Oracle.");
			 se.printStackTrace();
			 System.exit(1);
		      } 
                    }
                    if (this.interstop2 != null){
                    this.interstop2 = strings[4];
                    try{
                      Statement stmt = con.createStatement();
                      ResultSet result = stmt.executeQuery("SELECT cityid from cities where citystate='" + interstop2 + "'");
                      result.next();
                      interstop2 = result.getString("cityid");

                    }
                    catch (SQLException se ){
			 System.out.println("Unable to connect to Oracle.");
			 se.printStackTrace();
			 System.exit(1);
		      } 
                    }


                    try {
                        Statement stmt = con.createStatement();
                        
                        stmt.executeUpdate("INSERT INTO FLIGHT(bookDate, flightID, acType, destinationID, sourceID, departDate, arrivalDate, seatsBooked, numPassengers, departTime, arriveTime, inFlight, interstop1, interstop2)" + " VALUES('" + bookDate.getTimeInMillis() + "', '" + flightID + "', '" + type + "', '" + destination + "', '" + source + "', '" + deptDate.getTimeInMillis() + "', '" + arrivalDate.getTimeInMillis() + "', '" + seatsBooked + "', '" + numPassengers + "', '" + deptTime + "', '" + arrivalTime + "', '" + inFlight + "', '" + interstop1 + "', '" + interstop2 + "')");
                        stmt.close();
                    }
                    catch (SQLException ex) {
                        System.out.println("Error while executing query.");
                        ex.printStackTrace(System.err);
                        System.exit(1);
                    }

                    
		}
      
    /**
     * creates an airplane based off flightID so user can call functions
     *
     * @param ID - airplane ID
     */
     public Airplane(int ID){
          this.flightID = ID;
     }
    
   /**
     */ 
  public Airplane(){
  }
     
    /**
     * Cancels a flight, deleting all of its info from the database and returning the flightID
     *
     *  
     */
    public void cancelFlight(){
        
         // load jdbc driver
         try {
              Class.forName("oracle.jdbc.driver.OracleDriver");
         }
         catch(ClassNotFoundException ex) {
              ex.printStackTrace(System.err);
              System.exit(1);
         }
              
         // Establish the connection
         String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
         Connection con = null;
         try {
              con = DriverManager.getConnection(url, "bmill1", "bmill1");
              System.out.println("\nDeleting a flight.");
              System.out.println("---------------");
         }
         catch (SQLException se ){
              System.out.println("Unable   to connect to Oracle.");
              se.printStackTrace();
              System.exit(1);
         }
         
         //Delete the row containing the passengers info
        try {
          Statement stmt = con.createStatement();
          ResultSet result = stmt.executeQuery("SELECT flightId FROM FLIGHT WHERE flightID ='" + this.flightID + "'");
          result.next();
          this.flightID = result.getInt("flightId");
          stmt.executeUpdate("DELETE FROM FLIGHT WHERE flightID ='" + this.flightID + "'");
          stmt.close();
        }
        catch (SQLException ex) {
          System.out.println("Error while executing query.");
          ex.printStackTrace(System.err);
          System.exit(1);
        }
        
        
    }
  
  
    /**
    * Changes the departure time for a flight
    *
    */
    public void changeDepartureInfo(Calendar newDate, int newTime){
    
        // load jdbc driver
                try {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                }
                catch(ClassNotFoundException ex) {
                        ex.printStackTrace(System.err);
                        System.exit(1);
                }

                // Establish the connection
                String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
                Connection con = null;
                try {
                        con = DriverManager.getConnection(url, "bmill1", "bmill1");
                        System.out.println("\nChanging departure info.");
                        System.out.println("---------------");
                }
                catch (SQLException se ){
                        System.out.println("Unable to connect to Oracle.");
                        se.printStackTrace();
                        System.exit(1);
                }

                //Update the reservation status
                try {
                        Statement stmt = con.createStatement();
                       
                        stmt.executeUpdate("UPDATE FLIGHT SET departDate='" + newDate.getTimeInMillis() + "' WHERE flightID ='" + this.flightID + "'");
                        stmt.executeUpdate("UPDATE FLIGHT SET departTime='" + newTime + "' WHERE flightID ='" + this.flightID + "'");
                        stmt.close();
                }
                catch (SQLException ex) {
                        System.out.println("Error while executing query.");
                        ex.printStackTrace(System.err);
                        System.exit(1);
                }
      
    }
    
    /**
    * Changes the arrival time for a flight
    *
    */
    public void changeArrivalInfo(Calendar newDate, int newTime){
    
        // load jdbc driver
                try {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                }
                catch(ClassNotFoundException ex) {
                        ex.printStackTrace(System.err);
                        System.exit(1);
                }

                // Establish the connection
                String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
                Connection con = null;
                try {
                        con = DriverManager.getConnection(url, "bmill1", "bmill1");
                        System.out.println("\nChanging arrival info.");
                        System.out.println("---------------");
                }
                catch (SQLException se ){
                        System.out.println("Unable to connect to Oracle.");
                        se.printStackTrace();
                        System.exit(1);
                }

                //Update the reservation status
                try {
                        Statement stmt = con.createStatement();
                       
                        stmt.executeUpdate("UPDATE FLIGHT SET arrivalDate='" + newDate.getTimeInMillis() + "' WHERE flightID ='" + this.flightID + "'");
                        stmt.executeUpdate("UPDATE FLIGHT SET arriveTime='" + newTime + "' WHERE flightID ='" + this.flightID + "'");
                        
                        stmt.close();
                }
                catch (SQLException ex) {
                        System.out.println("Error while executing query.");
                        ex.printStackTrace(System.err);
                        System.exit(1);
                }
      
    }
 
 /**
   * This function has a flight arrive at its destination, generating and returning all necessary info
   * @return the necessary statistical values from the flight (number of passengers, number of stops, and time spent)
   */
        public int[] arrive(){
             
            int numstops = 0;
            int passengers = 0;
            long deptTime = 0;
            long arrivalTime = 0;
            long timeDiff = 0;
            int diffTime = 0;
            Calendar date = Calendar.getInstance();
            Calendar date2 = Calendar.getInstance();
            int[] values = new int[3];
         
            // load jdbc driver
                        try {
                                Class.forName("oracle.jdbc.driver.OracleDriver");
                        }
                        catch(ClassNotFoundException ex) {
                                ex.printStackTrace(System.err);
                                System.exit(1);
                        }

                        // Establish the connection
                        String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
                        Connection con = null;
                        try {
                                con = DriverManager.getConnection(url, "bmill1", "bmill1");
                                System.out.println("\nFlight is arriving.");
                                System.out.println("---------------");
                        }
                        catch (SQLException se ){
                                System.out.println("Unable to connect to Oracle.");
                                se.printStackTrace();
                                System.exit(1);
                        }
                       
            try{
              Statement stmt = con.createStatement();
             
              stmt.executeUpdate("UPDATE FLIGHT SET inFlight=0 WHERE flightID ='" + this.flightID + "'");
              ResultSet result = stmt.executeQuery("SELECT * FROM FLIGHT WHERE flightID ='" + this.flightID + "'");
              result.next();
              passengers = result.getInt("numPassengers");
              
              deptTime = result.getLong("departDate");
              arrivalTime = result.getLong("arrivalDate");
           
              
              //converts milliseconds to hours
              timeDiff = arrivalTime - deptTime;
              timeDiff = (timeDiff / (1000 * 60 * 60));
              
              diffTime = (int)timeDiff;
              
             
              if(result.getString("interstop1") != null && result.getString("interstop2") != null){
                numstops = 2;
              }
              else if(result.getString("interstop1") == null && result.getString("interstop2") == null){
                numstops = 0;
              }
              else{
                numstops = 1;
              }
              
              values[0] = passengers;
              values[1] = numstops;
              values[2] = diffTime;
              cancelFlight();
              
            }
            catch (SQLException ex) {
                System.out.println("Error while executing query.");
                ex.printStackTrace(System.err);
                System.exit(1);
            }
            return values;
        }
  
  public void takeOff(){
          // load jdbc driver
                try {
                        Class.forName("oracle.jdbc.driver.OracleDriver");
                }
                catch(ClassNotFoundException ex) {
                        ex.printStackTrace(System.err);
                        System.exit(1);
                }

                // Establish the connection
                String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
                Connection con = null;
                try {
                        con = DriverManager.getConnection(url, "bmill1", "bmill1");
                        System.out.println("\nFlight is taking off.");
                        System.out.println("---------------");
                }
                catch (SQLException se ){
                        System.out.println("Unable to connect to Oracle.");
                        se.printStackTrace();
                        System.exit(1);
                }
                
    try{
      Statement stmt = con.createStatement();
      stmt.executeUpdate("UPDATE FLIGHT SET inFlight='1' WHERE flightID ='" + this.flightID + "'");
      stmt.close();
    }
   catch (SQLException ex) {
                        System.out.println("Error while executing query.");
                        ex.printStackTrace(System.err);
                        System.exit(1);
                }
    
   }


/**
     * Gets an array of flights that have not taken off yet
     * 
     * @return - Integer array of flight ids
     */
    public Integer[] onGround(){
    
    	ArrayList <Integer> IDs = new ArrayList <Integer>();
    	
    	// load jdbc driver
    	try {
    		Class.forName("oracle.jdbc.driver.OracleDriver");
    	}
    	catch(ClassNotFoundException ex) {
    		ex.printStackTrace(System.err);
    		System.exit(1);
    	}
           
    	// Establish the connection
    	String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
    	Connection con = null;
    	try {
    		con = DriverManager.getConnection(url, "bmill1", "bmill1");
    		System.out.println("\nGetting planes on the ground.");
    		System.out.println("---------------");
    	}
    	catch (SQLException se ){
    		System.out.println("Unable   to connect to Oracle.");
    		se.printStackTrace();
    		System.exit(1);
    	}
                
    	try{
    		Statement stmt = con.createStatement();
    		
    		int counter = 0;
    		ResultSet result = stmt.executeQuery("SELECT flightId FROM FLIGHT WHERE inFlight =0");
    		while(result.next()){
    			IDs.add(new Integer(result.getInt("flightID")));
    			counter++;
    		}
    	}
    	catch (SQLException ex) {
    		System.out.println("Error while executing query.");
    		ex.printStackTrace(System.err);
    		System.exit(1);
    	}
    	Integer[] flights = new Integer[IDs.size()];
    	IDs.toArray(flights);
    	return flights;
  
    }	


    /**
     * Gets an array of flights that have not taken off yet
     * 
     * @return - Integer array of flight ids
     */
    public Integer[] inAir(){
    
    	ArrayList <Integer> IDs = new ArrayList <Integer>();
    	
    	// load jdbc driver
    	try {
    		Class.forName("oracle.jdbc.driver.OracleDriver");
    	}
    	catch(ClassNotFoundException ex) {
    		ex.printStackTrace(System.err);
    		System.exit(1);
    	}
           
    	// Establish the connection
    	String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
    	Connection con = null;
    	try {
    		con = DriverManager.getConnection(url, "bmill1", "bmill1");
    		System.out.println("\nFlights in the air.");
    		System.out.println("---------------");
    	}
    	catch (SQLException se ){
    		System.out.println("Unable   to connect to Oracle.");
    		se.printStackTrace();
    		System.exit(1);
    	}
                
    	try{
    		Statement stmt = con.createStatement();
    		
    		int counter = 0;
    		ResultSet result = stmt.executeQuery("SELECT flightId FROM FLIGHT WHERE inFlight =1");
    		while(result.next()){
    			IDs.add(new Integer(result.getInt("flightID")));
    			counter++;
    		}
    	}
    	catch (SQLException ex) {
    		System.out.println("Error while executing query.");
    		ex.printStackTrace(System.err);
    		System.exit(1);
    	}
    	Integer[] flights = new Integer[IDs.size()];
    	IDs.toArray(flights);
    	return flights;
  
    }	

     
  public int seatsAvailable(int flightNum){
    
    // load jdbc driver
    	try {
    		Class.forName("oracle.jdbc.driver.OracleDriver");
    	}
    	catch(ClassNotFoundException ex) {
    		ex.printStackTrace(System.err);
    		System.exit(1);
    	}
           
    	// Establish the connection
    	String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
    	Connection con = null;
    	try {
    		con = DriverManager.getConnection(url, "bmill1", "bmill1");
    		System.out.println("\nGetting seat available.");
    		System.out.println("---------------");
    	}
    	catch (SQLException se ){
    		System.out.println("Unable   to connect to Oracle.");
    		se.printStackTrace();
    		System.exit(1);
    	}
       
  try{
  
    
    Statement stmt = con.createStatement();
  
    ResultSet result = stmt.executeQuery("select seatsBooked from FLIGHT WHERE flightID = '" + this.flightID + "'");
    ResultSet result2 = stmt.executeQuery("select acType from FLIGHT WHERE flightID = '" + this.flightID + "'");
    result.next();
    result2.next();
    seatsBooked = result.getInt("seatsBooked");
    type = result2.getString("acType");
    stmt.close();
  }
  catch (SQLException ex) {
     System.out.println("Error while executing query.");
     ex.printStackTrace(System.err);
     System.exit(1);
  }
    if(type == "Boeing 737"){
      this.seatsAvailable = 124 - this.seatsBooked;
      return seatsAvailable;
    }
    
    else if(type == "Boeing 747"){
      this.seatsAvailable = 416 - this.seatsBooked;
      return seatsAvailable;
    }
    
    else if(type == "Boeing 767"){
      this.seatsAvailable = 375 - this.seatsBooked;
      return seatsAvailable;
    }
    
    else if(type == "Airbus 330"){
      this.seatsAvailable = 335 - this.seatsBooked;
      return seatsAvailable;
    }
    
    else if(type == "Airbus 340"){
      this.seatsAvailable = 375 - this.seatsBooked;
      return seatsAvailable;
    }
    
    else if(type == "Airbus 350"){
      this.seatsAvailable = 350 - this.seatsBooked;
      return seatsAvailable;
    }
    
    else if(type == "Airbus 380"){
      this.seatsAvailable = 525 - this.seatsBooked;
      return seatsAvailable;
    }
    else{
      return seatsAvailable;
    }
  
      
  }
  
  /**
    * This function increments the number of passengers in a flight, to be used when a passenger checks in  
    */
    public void addPassenger(){
            
            int numPassengers = 0;
            
            // load jdbc driver
            try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
            }
            catch(ClassNotFoundException ex) {
                    ex.printStackTrace(System.err);
                    System.exit(1);
            }

            // Establish the connection
            String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
            Connection con = null;
            try {
                    con = DriverManager.getConnection(url, "bmill1", "bmill1");
                    System.out.println("\nAdding a passenger.");
                    System.out.println("---------------");
            }
            catch (SQLException se ){
                    System.out.println("Unable to connect to Oracle.");
                    se.printStackTrace();
                    System.exit(1);
            }

            try{
                Statement stmt = con.createStatement();

                ResultSet result = stmt.executeQuery("SELECT numPassengers FROM FLIGHT WHERE flightID ='" + this.flightID + "'");
                result.next();
                numPassengers = result.getInt("numPassengers");
                numPassengers++;
                stmt.executeUpdate("UPDATE FLIGHT SET numPassengers='" + numPassengers + "' WHERE flightID ='" + this.flightID + "'");
                stmt.close();
            }
            catch (SQLException ex) {
                System.out.println("Error while executing query.");
                ex.printStackTrace(System.err);
                System.exit(1);
            }

            
        }
        
        
  /**
    * This function increments the number of seats booked in a flight, to be used when a passenger books a seat 
    */
    public void incrementSeatsBooked(){
            
            int seatsBooked = 0;
            
            // load jdbc driver
            try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
            }
            catch(ClassNotFoundException ex) {
                    ex.printStackTrace(System.err);
                    System.exit(1);
            }

            // Establish the connection
            String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";
            Connection con = null;
            try {
                    con = DriverManager.getConnection(url, "bmill1", "bmill1");
                    System.out.println("\nBooking a seat.");
                    System.out.println("---------------");
            }
            catch (SQLException se ){
                    System.out.println("Unable to connect to Oracle.");
                    se.printStackTrace();
                    System.exit(1);
            }

            try{
                Statement stmt = con.createStatement();

                ResultSet result = stmt.executeQuery("SELECT seatsBooked FROM FLIGHT WHERE flightID ='" + this.flightID + "'");
                result.next();
                
                seatsBooked = result.getInt("seatsBooked");
                seatsBooked++;
                stmt.executeUpdate("UPDATE FLIGHT SET seatsBooked='" + seatsBooked + "' WHERE flightID ='" + this.flightID + "'");
                stmt.close();
            }
            catch (SQLException ex) {
                System.out.println("Error while executing query.");
                ex.printStackTrace(System.err);
                System.exit(1);
            }

            
        }
	 
}